﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.IO;
using System.Data;
using NPOI.SS.UserModel;
using NPOI.SS.Util;
using NPOI.XSSF.UserModel;
using NPOI.HSSF.UserModel;
using System.Collections;

namespace ExcelUtility
{
    /// <summary>
    /// XSL 2023 03 Npoi 操作Excel。
    /// </summary>
    public class NpoiExcelHepler
    {
        /// <summary>
        /// 存放样式的哈希表，样式太多，Npoi将无法创建。
        /// </summary>
        Hashtable m_hasCellStyle = new Hashtable();
        /// <summary>
        /// 设置行的样式，四周的边框线，文字居中等。
        /// </summary>
        /// <param name="workbook"></param>
        /// <param name="sheet"></param>
        /// <param name="rowIndex">行索引，注意Npoi从0开始。</param>
        private void SetSytle(IWorkbook workbook, ISheet sheet, int rowIndex)
        {
            ICellStyle style3 = null;
            if (!m_hasCellStyle.Contains(3))
            {
                style3 = workbook.CreateCellStyle();
                style3.Alignment = NPOI.SS.UserModel.HorizontalAlignment.Center;
                style3.VerticalAlignment = VerticalAlignment.Center;
                style3.BorderBottom = NPOI.SS.UserModel.BorderStyle.Thin;
                style3.BorderLeft = NPOI.SS.UserModel.BorderStyle.None;
                style3.BorderRight = NPOI.SS.UserModel.BorderStyle.None;
                style3.BorderTop = NPOI.SS.UserModel.BorderStyle.Thin;
                m_hasCellStyle.Add(3, style3);
            }
            style3 = (ICellStyle)m_hasCellStyle[3];
            ICellStyle style4 = null;
            if (!m_hasCellStyle.Contains(4))
            {
                style4 = workbook.CreateCellStyle();
                style4.Alignment = NPOI.SS.UserModel.HorizontalAlignment.Center;
                style4.VerticalAlignment = VerticalAlignment.Center;
                style4.BorderBottom = NPOI.SS.UserModel.BorderStyle.Thin;
                style4.BorderLeft = NPOI.SS.UserModel.BorderStyle.None;
                style4.BorderRight = NPOI.SS.UserModel.BorderStyle.Thin;
                style4.BorderTop = NPOI.SS.UserModel.BorderStyle.Thin;
                m_hasCellStyle.Add(4, style4);
            }
            style4 = (ICellStyle)m_hasCellStyle[4];
            IRow row1 = sheet.CreateRow(rowIndex);
            for (int j = 0; j < 4; j++)
            {
                row1.CreateCell(j).CellStyle = style3;
            }
            row1.CreateCell(4).CellStyle = style4;
            for (int j = 5; j < 18; j++)
            {
                row1.CreateCell(j).CellStyle = style3;
            }
            row1.CreateCell(18).CellStyle = style4;
            for (int j = 19; j < 20; j++)
            {
                row1.CreateCell(j).CellStyle = style3;
            }
            row1.CreateCell(20).CellStyle = style4;
            for (int j = 21; j < 22; j++)
            {
                row1.CreateCell(j).CellStyle = style3;
            }
            row1.CreateCell(22).CellStyle = style4;
            for (int j = 23; j < 34; j++)
            {
                row1.CreateCell(j).CellStyle = style3;
            }
            row1.CreateCell(34).CellStyle = style4;
            for (int j = 35; j < 59; j++)
            {
                row1.CreateCell(j).CellStyle = style3;
            }
            row1.CreateCell(59).CellStyle = style4;
            for (int j = 60; j < 64; j++)
            {
                row1.CreateCell(j).CellStyle = style3;
            }
            row1.CreateCell(64).CellStyle = style4;
            for (int j = 65; j < 66; j++)
            {
                row1.CreateCell(j).CellStyle = style3;
            }
            row1.CreateCell(66).CellStyle = style4;
            for (int j = 67; j < 68; j++)
            {
                row1.CreateCell(j).CellStyle = style3;
            }
            row1.CreateCell(68).CellStyle = style4;
            for (int j = 69; j < 70; j++)
            {
                row1.CreateCell(j).CellStyle = style3;
            }
            row1.CreateCell(70).CellStyle = style4;
            for (int j = 71; j < 72; j++)
            {
                row1.CreateCell(j).CellStyle = style3;
            }
            row1.CreateCell(72).CellStyle = style4;
            for (int j = 73; j < 74; j++)
            {
                row1.CreateCell(j).CellStyle = style3;
            }
            row1.CreateCell(74).CellStyle = style4;
            for (int j = 75; j < 76; j++)
            {
                row1.CreateCell(j).CellStyle = style3;
            }
            row1.CreateCell(76).CellStyle = style4;
            for (int j = 77; j < 84; j++)
            {
                row1.CreateCell(j).CellStyle = style3;
            }
            row1.CreateCell(84).CellStyle = style4;
            for (int j = 85; j < 90; j++)
            {
                row1.CreateCell(j).CellStyle = style3;
            }
            row1.CreateCell(90).CellStyle = style4;
        }

        /// Description:  Inserts a existing row into a new row, will automatically push down
        ///               any existing rows.  Copy is done cell by cell and supports, and the
        ///               command tries to copy all properties available (style, merged cells, values, etc...)
        /// 
        private void CopyRow(IWorkbook workbook, ISheet worksheet, int sourceRowNum, int destinationRowNum)
        {
            // Get the source / new row
            IRow newRow = worksheet.GetRow(destinationRowNum);
            IRow sourceRow = worksheet.GetRow(sourceRowNum);
            // If the row exist in destination, push down all rows by 1 else create a new row
            if (newRow != null)
            {
                worksheet.ShiftRows(destinationRowNum, worksheet.LastRowNum, 1);
            }
            else
            {
                newRow = worksheet.CreateRow(destinationRowNum);
            }
            for (int i = 0; i < sourceRow.LastCellNum; i++)
            {
                ICell oldCell = sourceRow.GetCell(i);
                ICell newCell = newRow.CreateCell(i);
                // If the old cell is null jump to next cell
                if (oldCell == null)
                {
                    newCell = null;
                    continue;
                }
                // Copy style from old cell and apply to new cell
                 
                if(!m_hasCellStyle.Contains(oldCell.ColumnIndex))
                {
                    ICellStyle newCellStyle0 = workbook.CreateCellStyle();
                    newCellStyle0.CloneStyleFrom(oldCell.CellStyle);
                    m_hasCellStyle.Add(oldCell.ColumnIndex, newCellStyle0);
                }
                ICellStyle newCellStyle = (ICellStyle)m_hasCellStyle[oldCell.ColumnIndex];
                newCell.CellStyle = newCellStyle;
                // If there is a cell comment, copy
                if (newCell.CellComment != null) newCell.CellComment = oldCell.CellComment;
                // If there is a cell hyperlink, copy
                if (oldCell.Hyperlink != null) newCell.Hyperlink = oldCell.Hyperlink;
                // Set the cell data type
                newCell.SetCellType(oldCell.CellType);
                // Set the cell data value
                switch (oldCell.CellType)
                {
                    case CellType.Blank:
                        newCell.SetCellValue(oldCell.StringCellValue);
                        break;
                    case CellType.Boolean:
                        newCell.SetCellValue(oldCell.BooleanCellValue);
                        break;
                    case CellType.Error:
                        newCell.SetCellErrorValue(oldCell.ErrorCellValue);
                        break;
                    case CellType.Formula:
                        newCell.SetCellFormula(oldCell.CellFormula);
                        break;
                    case CellType.Numeric:
                        newCell.SetCellValue(oldCell.NumericCellValue);
                        break;
                    case CellType.String:
                        newCell.SetCellValue(oldCell.RichStringCellValue);
                        break;
                    case CellType.Unknown:
                        newCell.SetCellValue(oldCell.StringCellValue);
                        break;
                }
            }
            // If there are are any merged regions in the source row, copy to new row
            for (int i = 0; i < worksheet.NumMergedRegions; i++)
            {
                CellRangeAddress cellRangeAddress = worksheet.GetMergedRegion(i);
                if (cellRangeAddress.FirstRow == sourceRow.RowNum)
                {
                    CellRangeAddress newCellRangeAddress = new CellRangeAddress(newRow.RowNum,
                                                                                (newRow.RowNum +
                                                                                 (cellRangeAddress.FirstRow -
                                                                                  cellRangeAddress.LastRow)),
                                                                                cellRangeAddress.FirstColumn,
                                                                                cellRangeAddress.LastColumn);
                    worksheet.AddMergedRegion(newCellRangeAddress);
                }
            }

        }
        /// <summary>
        /// 导出特定格式的Excel文件。
        /// </summary>
        /// <param name="fileName">文件全路径名称。</param>
        /// <param name="dt">DataTable数据源组织后的数据表。</param>
        public void OutExcelBom(string fileName,DataTable dt)
         {
            string filepath = fileName;
            if (File.Exists(filepath))
            {
                FileStream file;
                ISheet sheet;
                IWorkbook workbook = null;
                try
                {
                    file = new FileStream(filepath, FileMode.Open, FileAccess.ReadWrite);
                    if (filepath.IndexOf(".xlsx") > 0)
                        workbook = new XSSFWorkbook(file);
                    else if (filepath.IndexOf(".xls") > 0)
                        workbook = new HSSFWorkbook(file); //new HSSFWorkbook
                    if (workbook != null)
                    {
                        sheet = workbook.GetSheet("标准目录");
                        sheet.ShiftRows(6, 6, dt.Rows.Count);
                        for (int i = 0; i < dt.Rows.Count; i++)
                        {
                            CellRangeAddress region1 = new CellRangeAddress(i + 6, i + 6, 0, 4);//P/#			
                            sheet.AddMergedRegion(region1);
                            CellRangeAddress region2 = new CellRangeAddress(i + 6, i + 6, 5, 18);// 图号(DRAWING No)
                            sheet.AddMergedRegion(region2);
                            CellRangeAddress region3 = new CellRangeAddress(i + 6, i + 6, 19, 20);// TP1
                            sheet.AddMergedRegion(region3);
                            CellRangeAddress region4 = new CellRangeAddress(i + 6, i + 6, 21, 22);// TP2
                            sheet.AddMergedRegion(region4);
                            CellRangeAddress region5 = new CellRangeAddress(i + 6, i + 6, 23, 34);//名称 （NAME）
                            sheet.AddMergedRegion(region5);
                            CellRangeAddress region6 = new CellRangeAddress(i + 6, i + 6, 35, 59);//规格(TYPE)
                            sheet.AddMergedRegion(region6);
                            CellRangeAddress region7 = new CellRangeAddress(i + 6, i + 6, 60, 64);//分组号
                            sheet.AddMergedRegion(region7);
                            CellRangeAddress region8 = new CellRangeAddress(i + 6, i + 6, 65, 66);//G01
                            sheet.AddMergedRegion(region8);
                            CellRangeAddress region9 = new CellRangeAddress(i + 6, i + 6, 67, 68);//G02
                            sheet.AddMergedRegion(region9);
                            CellRangeAddress region10 = new CellRangeAddress(i + 6, i + 6, 69, 70);//G03
                            sheet.AddMergedRegion(region10);
                            CellRangeAddress region11 = new CellRangeAddress(i + 6, i + 6, 71, 72);//G04
                            sheet.AddMergedRegion(region11);
                            CellRangeAddress region12 = new CellRangeAddress(i + 6, i + 6, 73, 74);//G05
                            sheet.AddMergedRegion(region12);
                            CellRangeAddress region13 = new CellRangeAddress(i + 6, i + 6, 75, 76);//G06
                            sheet.AddMergedRegion(region13);
                            CellRangeAddress region14 = new CellRangeAddress(i + 6, i + 6, 77, 84);//制造商
                            sheet.AddMergedRegion(region14);
                            CellRangeAddress region15 = new CellRangeAddress(i + 6, i + 6, 85, 90);//备注
                            sheet.AddMergedRegion(region15);
                            SetSytle(workbook, sheet, i + 6);
                        }
                        //模板中已经有了，不再需要。
                        //sheet.GetRow(1).GetCell(0).SetCellValue(@"小松（大连）机床有限公司");
                        if (dt.Rows.Count > 0)
                        {
                            string sUser = (string)dt.Rows[0]["申请人"];
                            string sAuditor = dt.Rows[0]["审核人"]!=System.DBNull.Value?(string)dt.Rows[0]["审核人"]:"";
                            sheet.GetRow(1).GetCell(35).SetCellValue(sUser);
                            sheet.GetRow(1).GetCell(45).SetCellValue(sAuditor);
                            string sDate = DateTime.Now.ToString("dd/MM/yyyy");
                            sheet.GetRow(1).GetCell(55).SetCellValue(sDate);
                            string sJCXH = (string)dt.Rows[0]["机床型号"];
                            sheet.GetRow(2).GetCell(23).SetCellValue(sJCXH);
                            string sAh = dt.Rows[0]["A号"] == System.DBNull.Value ? "" : (string)dt.Rows[0]["A号"];
                            sheet.GetRow(3).GetCell(5).SetCellValue(sAh);
                            string th= dt.Rows[0]["图纸编号"].ToString();
                            th=th.PadLeft(6, '0');
                            string s = /*dt.Rows[0]["图纸类型"].ToString() +*/ th;
                            string sName = (string)dt.Rows[0]["名称"];
                            sheet.GetRow(3).GetCell(23).SetCellValue(s);
                            sheet.GetRow(3).GetCell(41).SetCellValue(sName);
                            //首行放装配
                            for (int i = 0; i < dt.Rows.Count; i++)
                            {
                                if (dt.Rows[i]["图纸编号"] != System.DBNull.Value)
                                {
                                    th = dt.Rows[i]["图纸编号"].ToString();
                                    //th = th.PadLeft(6, '0');
                                    string s1 = /*dt.Rows[i]["图纸类型"].ToString() +*/ th + (i == 0 ? "" : "-001");
                                    sheet.GetRow(i + 6).GetCell(5).SetCellValue(s1);
                                    string sPartName = dt.Rows[i]["名称"] == System.DBNull.Value ? "" : (string)dt.Rows[i]["名称"];
                                    sheet.GetRow(i + 6).GetCell(23).SetCellValue(sPartName);
                                    string sGuige = dt.Rows[i]["规格"] == System.DBNull.Value ? "" : (string)dt.Rows[i]["规格"];
                                    sheet.GetRow(i + 6).GetCell(35).SetCellValue(sGuige);
                                    sheet.GetRow(i + 6).GetCell(0).SetCellValue((i + 1).ToString());
                                }
                            }
                        }
                        file = new FileStream(filepath, FileMode.Open, FileAccess.Write);
                        workbook.Write(file);
                        file.Close();
                        workbook.Close();
                    }
                }
                catch (System.Exception ex)
                {
                    throw ex;
                }
            }
        }

        /// <summary>
        /// 导出特定格式的Excel文件。
        /// </summary>
        /// <param name="fileName">文件全路径名称。</param>
        /// <param name="dt">DataTable数据源组织后的数据表。保留原表结构</param>
        public string OutExcelBomHoldOnOld(string fileName, DataTable dt)
        {
            string filepath = fileName;
            string sRet = "OK";
            if (File.Exists(filepath))
            {
                FileStream file;
                ISheet sheet;
                IWorkbook workbook = null;
                try
                {
                    file = new FileStream(filepath, FileMode.Open, FileAccess.ReadWrite);
                    if (filepath.IndexOf(".xlsx") > 0)
                        workbook = new XSSFWorkbook(file);
                    else if (filepath.IndexOf(".xls") > 0)
                        workbook = new HSSFWorkbook(file);
                    if (workbook != null)
                    {
                        sheet = workbook.GetSheet("标准目录");
                        if (sheet.LastRowNum - 6 < dt.Rows.Count)
                        {
                            //file = new FileStream(filepath, FileMode.Open, FileAccess.Write);
                            //workbook.Write(file);
                            file.Close();
                            workbook.Close();
                            return "目录数量大于原有Excel表格的数量，无法保留，请重新生成";
                        }
                        //原有表，不用Shift
                        //sheet.ShiftRows(6, 6, dt.Rows.Count);
                        //for (int i = 0; i < dt.Rows.Count; i++)
                        //{
                        //    CellRangeAddress region1 = new CellRangeAddress(i + 6, i + 6, 0, 4);//P/#			
                        //    sheet.AddMergedRegion(region1);
                        //    CellRangeAddress region2 = new CellRangeAddress(i + 6, i + 6, 5, 18);// 图号(DRAWING No)
                        //    sheet.AddMergedRegion(region2);
                        //    CellRangeAddress region3 = new CellRangeAddress(i + 6, i + 6, 19, 20);// TP1
                        //    sheet.AddMergedRegion(region3);
                        //    CellRangeAddress region4 = new CellRangeAddress(i + 6, i + 6, 21, 22);// TP2
                        //    sheet.AddMergedRegion(region4);
                        //    CellRangeAddress region5 = new CellRangeAddress(i + 6, i + 6, 23, 34);//名称 （NAME）
                        //    sheet.AddMergedRegion(region5);
                        //    CellRangeAddress region6 = new CellRangeAddress(i + 6, i + 6, 35, 59);//规格(TYPE)
                        //    sheet.AddMergedRegion(region6);
                        //    CellRangeAddress region7 = new CellRangeAddress(i + 6, i + 6, 60, 64);//分组号
                        //    sheet.AddMergedRegion(region7);
                        //    CellRangeAddress region8 = new CellRangeAddress(i + 6, i + 6, 65, 66);//G01
                        //    sheet.AddMergedRegion(region8);
                        //    CellRangeAddress region9 = new CellRangeAddress(i + 6, i + 6, 67, 68);//G02
                        //    sheet.AddMergedRegion(region9);
                        //    CellRangeAddress region10 = new CellRangeAddress(i + 6, i + 6, 69, 70);//G03
                        //    sheet.AddMergedRegion(region10);
                        //    CellRangeAddress region11 = new CellRangeAddress(i + 6, i + 6, 71, 72);//G04
                        //    sheet.AddMergedRegion(region11);
                        //    CellRangeAddress region12 = new CellRangeAddress(i + 6, i + 6, 73, 74);//G05
                        //    sheet.AddMergedRegion(region12);
                        //    CellRangeAddress region13 = new CellRangeAddress(i + 6, i + 6, 75, 76);//G06
                        //    sheet.AddMergedRegion(region13);
                        //    CellRangeAddress region14 = new CellRangeAddress(i + 6, i + 6, 77, 84);//制造商
                        //    sheet.AddMergedRegion(region14);
                        //    CellRangeAddress region15 = new CellRangeAddress(i + 6, i + 6, 85, 90);//备注
                        //    sheet.AddMergedRegion(region15);
                        //    SetSytle(workbook, sheet, i + 6);
                        //}
                        if (dt.Rows.Count > 0)
                        {
                            //表头也不用替换了
                            //string sUser = (string)dt.Rows[0]["申请人"];
                            //string sAuditor = dt.Rows[0]["审核人"] != System.DBNull.Value ? (string)dt.Rows[0]["审核人"] : "";
                            //sheet.GetRow(1).GetCell(35).SetCellValue(sUser);
                            //sheet.GetRow(1).GetCell(45).SetCellValue(sAuditor);
                            //string sDate = DateTime.Now.ToString("dd/MM/yyyy");
                            //sheet.GetRow(1).GetCell(55).SetCellValue(sDate);
                            //string sJCXH = (string)dt.Rows[0]["机床型号"];
                            //sheet.GetRow(2).GetCell(23).SetCellValue(sJCXH);
                            //string sAh = dt.Rows[0]["A号"] == System.DBNull.Value ? "" : (string)dt.Rows[0]["A号"];
                            //sheet.GetRow(3).GetCell(5).SetCellValue(sAh);
                            //string th = dt.Rows[0]["图纸编号"].ToString();
                            //th = th.PadLeft(6, '0');
                            //string s = dt.Rows[0]["图纸类型"].ToString() + th;
                            //string sName = (string)dt.Rows[0]["名称"];
                            //sheet.GetRow(3).GetCell(23).SetCellValue(s);
                            //sheet.GetRow(3).GetCell(41).SetCellValue(sName);
                            //首行放装配
                            string th = string.Empty;
                            for (int i = 0; i < dt.Rows.Count; i++)
                            {
                                string sOld = sheet.GetRow(i + 6).GetCell(5).ToString();
                                //if (!string.IsNullOrEmpty(sOld))
                                {
                                    if (dt.Rows[i]["图纸编号"] != System.DBNull.Value)
                                    {
                                        th = dt.Rows[i]["图纸编号"].ToString();
                                        th = th.PadLeft(6, '0');
                                        string s1 = /*dt.Rows[i]["图纸类型"].ToString() +*/ th + (i == 0 ? "" : "-001");
                                        //if (s1 == sOld)
                                        {
                                            sheet.GetRow(i + 6).GetCell(5).SetCellValue(s1);
                                            string sPartName = dt.Rows[i]["名称"] == System.DBNull.Value ? "" : (string)dt.Rows[i]["名称"];
                                            sheet.GetRow(i + 6).GetCell(23).SetCellValue(sPartName);
                                            string sGuige = dt.Rows[i]["规格"] == System.DBNull.Value ? "" : (string)dt.Rows[i]["规格"];
                                            sheet.GetRow(i + 6).GetCell(35).SetCellValue(sGuige);
                                        }
                                    }
                                    sheet.GetRow(i + 6).GetCell(0).SetCellValue((i + 1).ToString());
                                }
                            }
                        }
                        file = new FileStream(filepath, FileMode.Open, FileAccess.Write);
                        workbook.Write(file);
                        file.Close();
                        workbook.Close();
                    }
                }
                catch (System.Exception ex)
                {
                    sRet = ex.Message;
                    throw ex;
                }
                return sRet;
            }
            return sRet;
        }
    }
}
